/*********************************************************************************
 *      Copyright:  (C) 2023 iot<iot@email.com>
 *                  All rights reserved.
 *
 *       Filename:  sql_client.c
 *    Description:  This file is the use of the interface to the SQliite3
 *                 
 *        Version:  1.0.0(03/21/23)
 *         Author:  iot <iot@email.com>
 *      ChangeLog:  1, Release initial version on "03/21/23 02:06:58"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>

#include "sql_client.h"
#include "sqlite3.h"
#include "logger.h"
#include "data_process.h"

static sqlite3    *db = NULL;

/* creat a database */
int creat_db(char *db_name)
{
	char		*zErrMsg = NULL;
	char        buf[256];

	if( !db_name )
	{
		log_error("error:invalid input argument\n");
		return -1;
	}

	if( SQLITE_OK != sqlite3_open(db_name,&db) )
	{
		log_error("error open database:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_info("open database successful\n");

		memset(buf,0,sizeof(buf));
		snprintf(buf, sizeof(buf),"CREATE TABLE IF NOT EXISTS %s(ID INTEGER PRIMARY KEY AUTOINCREMENT, SN CHAR(20),DATIME CHAR(100),TEMPERATURE FLOAT);", TABLE_NAME);
		
		log_info("message:%s\n",buf);

		if( SQLITE_OK != sqlite3_exec(db,buf,callback,0,&zErrMsg) )
    	{
        	log_error("creat table error:%s\n",zErrMsg);
        	sqlite3_free(zErrMsg);
        	return -3;
    	}
    	else
    	{
        	log_info("creat table successfully\n");
        	return 0;
    	}

		return 0;
	}
}

/*callback function*/
int callback(void *NotUsed,int argc,char **argv,char **azColName)
{
	int i;

	for( i=0;i<argc;i++ )
	{
		log_info("%s = %s\n",azColName[i],argv[i] ? argv[i] : "NULL");
	}
	return 0;
}


/*insert table into the database*/
int insert_db(sample_data_t sample_data)
{
	char		buf_insert[256];
	char		*zErrMsg = NULL;
	
	memset(buf_insert,0,sizeof(buf_insert));
	snprintf(buf_insert,sizeof(buf_insert),"INSERT INTO %s VALUES (NULL,'%s','%s',%f);",TABLE_NAME,sample_data.devid,sample_data.time,sample_data.temp);
	log_info("message:%s\n",buf_insert);

	if( SQLITE_OK != sqlite3_exec(db,buf_insert,callback,0,&zErrMsg) )
	{
		log_error("sqlite insert table error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	else
	{
		log_info("insert table successfully\n");
		return 0;
	}

}

int select_db(sample_data_t *sample_data)
{
    char                    buf_select[128];
    sqlite3_stmt            *pStmt = NULL;
    int                     rowid
    int                     rv = 0;

    memset(buf_select,0,sizeof(buf_select));
    snprintf(buf_select,sizeof(buf_select),"SELECT rowid FROM %s LIMIT 1;",TABLE_NAME);

    if( SQLITE_OK != (sqlite3_prepare_v2(db, buf_select,-1,&pStmt,NULL)) );
    {
        log_error("SELECT rowid error\n");
        return -1;
    }
    else
    {
        if( SQLITE_ROW == sqlite3_step(pStmt) )
        {
            rowid = sqlite3_column_int(pStmt,0);
        }
        else
        {
            return -2;
        }
        
    }

    if( !rowid )
    {
        sqlite3_finalize(pStmt);
        return -3;
    }
    sqlite3_finalize(pStmt);

    memset(buf_select,0,sizeof(buf_select));
    snprintf(buf_select,sizeof(buf_select),"SELECT Data FROM %s LIMIT 1;",TABLE_NAME);

    if( SQLITE_OK != (sqlite3_prepare_v2(db,buf_select,-1;&pStmt,NULL)) )
    {
        log_error("failed to prepare stastement\n");
        close_db();
        sqlite3_finalize(pStmt);   
        return -4;
    }

    rv = sqlite3_step(pStmt);
    sample_data_t   *sample_buf = (sample_data_t*)sqlite3_column_blob(pStmt,0);
    bytes = sqlite3_column_bytes(pStmt,0);
    
    memcpy(sample_data->devid,sample_buf->devid,strlen(sample_buf->devid)+1);
    memcpy(sample_data->time,sample_buf->time,strlen(sample_buf->time)+1);
    sample_data->temp = sample_buf->temp;

    return bytes;
}
 
int delete_db(void)
{
	char                     buf_deldata[128];
	char                    *zErrMsg=NULL;
	
	memset(buf_deldata,0,sizeof(buf_deldata));
	snprintf(buf_deldata,sizeof(buf_deldata),"DELETE from %s where ID = (SELECT MIN(ID) from %s);",TABLE_NAME,TABLE_NAME);
	 
	if( SQLITE_OK != sqlite3_exec(db,buf_deldata,0,0,&zErrMsg) )
	{
		log_error("delete data from sqlite error:%s\n ",zErrMsg);		 
		sqlite3_free(zErrMsg);
		return -1;
	}
	 
	log_info("delete successfully\n");
	return 0;
}

/*close the database*/
int close_db(void)
{
	char *zErrMsg = NULL;
	int error_num;
	
	error_num = sqlite3_close(db);
    
	if( SQLITE_OK != error_num )
	{
		log_error("error close database:%s error_number:%d\n",zErrMsg,error_num);
		sqlite3_free(zErrMsg);
		return -1;
	}
	else
	{
		log_info("successfully close database\n");
		return 0;
	}
}
